import pymysql
from flask import jsonify


class MysqlHandle:
    def __init__(self):
        self.dst_table = ''
        self.conn = pymysql.connect(
            host="127.0.0.1",
            port=3306,
            database="ferryway",
            charset="utf8",
            user="root",
            passwd="Jdwa*2021")

    def handle_sql_all(self, sql):
        try:
            with self.conn.cursor() as cursor:
                cursor.execute(sql)
                datas = cursor.fetchall()
        except Exception as e:
            print(e)
            return ' '
        print(datas)
        return datas

    def handle_sql_one(self, sql):
        try:
            with self.conn.cursor() as cursor:
                cursor.execute(sql)
                datas = cursor.fetchone()
        except Exception as e:
            print(e)
            return ' '
        print(datas)
        return datas

    def gen_dst_table(self, flag):
        if flag == 1:
            self.dst_table = 'udp_multicast_send'
        else:
            self.dst_table = 'udp_multicast_receiver'

    # 获取流量
    def get_traffic(self, flag):

        ids = self.get_ids_data(flag)
        new_id_data = []
        for id in ids:
            sql = "select udp_multicast_id,byte from udp_multicast_traffic WHERE flag= {} and udp_multicast_id = {}" \
                  " ORDER BY id desc LIMIT 1".format(flag, id[0])
            print(sql)
            datas = self.handle_sql_one(sql)
            new_id_data.append(datas)

        return new_id_data

    # 获取流速
    def get_speed(self, id, flag, start_time, end_time):

        sql = "select * from (select udp_multicast_id,from_unixtime(tm, '%Y-%m-%d %H:%i:%S'),speed,id from udp_multicast_traffic_speed  WHERE udp_multicast_id= {} and flag= {} and tm>={} and tm<={} ORDER BY id desc LIMIT 20) as t order by id;".format(
            id, flag, start_time, end_time)

        return self.handle_sql_all(sql)

    # 获取最新10个id
    def get_ids_data(self, flag):
        self.gen_dst_table(flag)
        sql = "select id  from {} order by id desc limit 10".format(self.dst_table)
        return self.handle_sql_all(sql)

    # 获取实时最新数据
    def get_retime_data(self, flag):
        self.gen_dst_table(flag)
        sql = "select  t.tm,t.byte,t.pkts,s.speed ,t.id  from (select tm,byte,pkts,id from udp_multicast_traffic where flag = {} order by id desc limit 1) as t LEFT OUTER JOIN   (select speed,id from udp_multicast_traffic_speed where flag = {} order by id desc limit 1) as s ON t.id = s.id;".format(
            flag,flag)

        return self.handle_sql_all(sql)


if __name__ == '__main__':
    # MysqlHandle().get_speed(6,1,1665984431,1665990308)
    MysqlHandle().get_retime_data(1)
